package com.admin.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.admin.bean.AdminSpBean;
import com.admin.util.SqlUtil;

@Repository
public class AdminSpDao {

	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private QueryRunner queryRunner;
	
	
	public int addAdminSp(AdminSpBean spBean){
		String sql = "insert into adminsp(spname,spcodename,bussiness,spcode,spcommand,spprovince,spfee,spmt,spdaylimit,spmonthlimit,spmemo,spstatus,companyid,spisp,shieldcity,spdesc,createdtime) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,getDate())";
		return jdbcTemplate.update(sql, new Object[]{spBean.getSpname(),spBean.getSpcodename(),spBean.getBussiness(),
				spBean.getSpcode(),spBean.getSpcommand(),spBean.getSpprovince(),
				spBean.getSpfee(),spBean.getSpmt(),spBean.getSpdaylimit(),spBean.getSpmonthlimit(),
				spBean.getSpmemo(),spBean.getSpstatus(),spBean.getCompanyid(),spBean.getSpisp(),spBean.getShieldcity(),spBean.getSpdesc()});
	}
	
	
	public int updateAdminSp(AdminSpBean spBean){
		String sql = "update adminsp set spname = ?,spcodename = ?,bussiness = ?,spcode = ?,spcommand = ?,spprovince = ?,spfee = ?,spmt = ?,spdaylimit = ?,spmonthlimit = ?,spmemo = ?,spstatus = ?, companyid = ?,spisp = ?,shieldcity=?,spdesc=? where id = ?";
		return jdbcTemplate.update(sql, new Object[]{spBean.getSpname(),spBean.getSpcodename(),spBean.getBussiness(),
				spBean.getSpcode(),spBean.getSpcommand(),spBean.getSpprovince(),
				spBean.getSpfee(),spBean.getSpmt(),spBean.getSpdaylimit(),spBean.getSpmonthlimit(),
				spBean.getSpmemo(),spBean.getSpstatus(),spBean.getCompanyid(),spBean.getSpisp(),spBean.getShieldcity(),spBean.getSpdesc(),spBean.getId()});
	}
	
	
	public int delAdminSp(int id){
		final String sql = "delete from adminsp where id = ?";
		try {
			return jdbcTemplate.update(sql, new Object[]{id});
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	
	
	
	
	public AdminSpBean findAdminSpById(int id) throws SQLException {
		final String sql = "select * from adminsp where id = ?";
		return queryRunner.query(sql, new BeanHandler<AdminSpBean>(AdminSpBean.class), new Object[]{id});
	}
	
	public int findCount(){
		final String sql = "select count(0) from adminsp";
		return jdbcTemplate.queryForInt(sql);
	}
	
	public int findCount(String condition){
		final String sql = "select count(0) from adminsp where " + condition;
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List<AdminSpBean> findList(int start,int pageSize) throws SQLException{
		final String sql = SqlUtil.getPageSqlForSqlServerAsc("*", "id", "adminsp", "id", "1=1", start, pageSize);//"select * from company order by companyId desc limit ?,?";
		return queryRunner.query(sql, new BeanListHandler<AdminSpBean>(AdminSpBean.class),new Object[]{});
	}
	
	public List<AdminSpBean> findList(String conditon,int start,int pageSize) throws SQLException{
		final String sql = SqlUtil.getPageSqlForSqlServerAsc("*", "id", "adminsp", "id", conditon, start, pageSize);//"select * from company order by companyId desc limit ?,?";
		return queryRunner.query(sql, new BeanListHandler<AdminSpBean>(AdminSpBean.class),new Object[]{});
	}
	
	public int findCountByCompanyId(int companyId){
		final String sql = "select count(0) from adminsp where companyid = "+companyId;
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List<AdminSpBean> findListByCompanyId(int start,int pageSize,int companyId) throws SQLException{
		final String sql = SqlUtil.getPageSqlForSqlServerAsc("*", "id", "adminsp", "id", "companyid="+companyId, start, pageSize);//"select * from company order by companyId desc limit ?,?";
		return queryRunner.query(sql, new BeanListHandler<AdminSpBean>(AdminSpBean.class),new Object[]{});
	}
	
	
	public int findCountBySpCode(String spCode){
		final String sql = "select count(0) from adminsp where spcode like '%"+spCode+"%'";
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List<AdminSpBean> findListBySpCode(int start,int pageSize,String spCode) throws SQLException{
		final String sql = SqlUtil.getPageSqlForSqlServerAsc("*", "id", "adminsp", "id", "spcode like '%"+spCode+"%'", start, pageSize);//"select * from company order by companyId desc limit ?,?";
		return queryRunner.query(sql, new BeanListHandler<AdminSpBean>(AdminSpBean.class),new Object[]{});
	}
	
	
	public int findCountBySpName(String spName){
		final String sql = "select count(0) from adminsp where spname like '%"+spName+"%'";
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List<AdminSpBean> findListBySpName(int start,int pageSize,String spName) throws SQLException{
		final String sql = SqlUtil.getPageSqlForSqlServerAsc("*", "id", "adminsp", "id", "spcode like '%"+spName+"%'", start, pageSize);//"select * from company order by companyId desc limit ?,?";
		return queryRunner.query(sql, new BeanListHandler<AdminSpBean>(AdminSpBean.class),new Object[]{});
	}
}
